﻿--select * from [dbo].[X_SELLING_I2B]
declare @month nvarchar(6) = (select MAX_MONTH from DATE_EM_REPORT)

delete from SME_XSELLING_FROM_ANHHD3
exec('
Insert SME_XSELLING_FROM_ANHHD3
SELECT *  FROM [SERVER12].[CROSSELLING_SME].[dbo].[SME_CROSS_SELLING_'+@month+']')

--select * from SME_XSELLING_FROM_ANHHD3
delete from SME_XSELLING_FROM_ANHHD3 where lift < 1

update SME_XSELLING_FROM_ANHHD3
set rhs = RTRIM(LTRIM(rhs))
--select distinct(right(left(rhs, len(rhs)-1),len(rhs)-2) ) from SME_XSELLING_FROM_ANHHD3
update SME_XSELLING_FROM_ANHHD3
set rhs = right(left(rhs, len(rhs)-1),len(rhs)-2)

EXEC ('IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = ''product_anhdh3'' AND TYPE = ''U'')
       DROP TABLE product_anhdh3')

select distinct rhs into product_anhdh3 from SME_XSELLING_FROM_ANHHD3 

delete from TBL_X_SELLING_PRODUCT
insert TBL_X_SELLING_PRODUCT
select A.PRODUCT_NAME, 1, A.PRODUCT_NAME_EN, 0 , 0, a.PRODUCT from TBL_PRODUCT_SME a, product_anhdh3 b
where a.PRODUCT = b.rhs

--select * from TBL_PRODUCT_SME a, product_anhdh3 b
--where a.PRODUCT = b.rhs

--select a.rhs,1, a.rhs, 0, 0, a.rhs from product_anhdh3 a
--left join TBL_X_SELLING_PRODUCT b on a.rhs=b.PRODUCT_CODE
--select * from TBL_X_SELLING_PRODUCT
--SELECT * FROM TBL_X_SELLING_CUSTOMER
--DELETE FROM TBL_X_SELLING_CUSTOMER WHERE PRODUCT_ID <> 4

DELETE FROM TBL_X_SELLING_CUSTOMER

INSERT TBL_X_SELLING_CUSTOMER
SELECT A.CIF, C.CUS_NAME, C.DAO, C.dao_name, C.BRANCH_ID, D.BRANCH_NAME_SME, D.REGION,C.INDUSTRY_NAME, C.INDUSTRY_NAME_EN, C.CUS_OPEN_DATE, A.LIFT, A.CONFIDENCE, B.ID
FROM SME_XSELLING_FROM_ANHHD3 A, TBL_X_SELLING_PRODUCT B, TBL_CUSTOMER C, TBL_BRANCH D
WHERE A.RHS = B.PRODUCT_CODE AND A.CIF= C.CIF AND C.BRANCH_ID = D.BRANCH_ID ORDER BY B.ID

DELETE FROM TBL_X_SELLING_CUSTOMER
WHERE CIF IN (SELECT CIF FROM TBL_LIST_BAD_BANK)
-- where PRODUCT_ID = 5 
 update a
	 set Min_lift = b.minLift, max_lift= b.maxLift
	 from TBL_X_SELLING_PRODUCT a, (select min(LIFT_INDEX) as minLift, MAX(LIFT_INDEX) as maxLift, PRODUCT_ID from TBL_X_SELLING_CUSTOMER group by PRODUCT_ID)  b
	 where a.ID = b.PRODUCT_ID

--INSERT TBL_DATE_BUSINESS
--select MAX_DATE_ENDMONTH, 'TBL_X_SELLING_CUSTOMER' from DATE_EM_REPORT

update b
set b.date_data = (select MAX_DATE_ENDMONTH from DATE_EM_REPORT)
from TBL_DATE_BUSINESS b
where b.name_table = 'TBL_X_SELLING_CUSTOMER'

--delete from server12.SMECustomer360.[dbo].TBL_X_SELLING_CUSTOMER
--insert server12.SMECustomer360.dbo.TBL_X_SELLING_CUSTOMER
--select * from TBL_X_SELLING_CUSTOMER

--delete from server12.SMECustomer360.[dbo].TBL_X_SELLING_PRODUCT
--insert server12.SMECustomer360.dbo.TBL_X_SELLING_PRODUCT
--select * from TBL_X_SELLING_PRODUCT
select * from TBL_DATE_BUSINESS
	 